package bbs;

import java.io.CharArrayReader;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import oracle.sql.CLOB;

public class BBS {
	
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String db_user = "semi";
	String db_pwd = "1234";
	Connection con; 
	PreparedStatement psmt;
	ResultSet rs;
	BBSVo bv;
	
	public BBS() {
		try {
			Class.forName(driver).newInstance();
			con = DriverManager.getConnection(url, db_user, db_pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public BBSVo getBv() {
		return bv;
	}
	public void setBv(BBSVo bv) {
		this.bv = bv;
	}

	public boolean insert(){
		boolean flag=false;
		try {
			
			String sql1="insert into se(num,mid,pwd,title,content) values(seq_no.nextval,?,?,?,empty_clob())";
			String sql2="select seq_no.currval from dual";
			String sql3="select content from se where num=? for update";
			con.setAutoCommit(false);
			//일단 데이터를 저장한다. clob타입의 자리엔 empty_clob()를 이용하여 데이타를 넣을 공간을 확보한다.
			psmt = con.prepareStatement(sql1);
			psmt.setString(1, bv.getMid());
			psmt.setString(2, bv.getPwd());
			psmt.setString(3, bv.getTitle());
			int cnt1=psmt.executeUpdate();
			psmt.close();
			
			//sql3가 select문인데 select문에는 시퀀스명.currval을 사용하지 못하기 때문에 현재의 시퀀스 값을 얻어오기 위해 사용
			psmt = con.prepareStatement(sql2);
			rs = psmt.executeQuery();
			rs.next();
			int no = rs.getInt(1);
			
			//방금 sql1에 insert한 데이타를 가져온다. 
			psmt = con.prepareStatement(sql3);
			psmt.setInt(1, no);
			rs = psmt.executeQuery();
			if(rs.next()){
				//rs의 메소드 중 getClob를 통해 clob타입의 데이타를 가져온다.
				Clob clob= rs.getClob("content");
				//clob타입과 연결
				Writer writer = ((CLOB)clob).getCharacterOutputStream();
				//clob에 넣을 문자를 char형의 배열로 만들어서 읽어들임
				Reader src = new CharArrayReader(bv.getContent().toCharArray());
				char[] buffer = new char[1024];
				int read=0;
				//1024byte씩 읽어들인 데이타의 끝까지 반복함
				while((read = src.read(buffer,0,1024))!=-1){
					writer.write(buffer,0,read);//clob에 데이타를 넣는역할인듯
				}
				src.close();
				writer.close();
			}
			if(cnt1>0) flag=true;
			con.commit();
			con.setAutoCommit(true);
			rs.close();
			psmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean update(){
		boolean flag=false;
		try {
		
			String sql1="update se set title=?, content=empty_clob() where num=? and pwd=?";
			String sql2="select content from se where num=? for update";
			
			con.setAutoCommit(false);
			//sql1 스마트 에디터 내용을 제외한 나머지 값들을 수정한다
			psmt = con.prepareStatement(sql1);
			psmt.setString(1, bv.getTitle());
			psmt.setInt(2, bv.getNum());
			psmt.setString(3, bv.getPwd());
			int cnt=psmt.executeUpdate();
			psmt.close();
			//그 수정한 데이타의 clob 타입을 가져온다.
			//나머지는 insert에 한거랑 동일
			psmt = con.prepareStatement(sql2);
			psmt.setInt(1, bv.getNum());
			rs = psmt.executeQuery();
			if(rs.next()){
				Clob clob= rs.getClob("content");
				Writer writer = ((CLOB)clob).getCharacterOutputStream();
				Reader src = new CharArrayReader(bv.getContent().toCharArray());
				char[] buffer = new char[1024];
				int read=0;
				while((read = src.read(buffer,0,1024))!=-1){
					writer.write(buffer,0,read);
				}
				src.close();
				writer.close();
			}
			if(cnt>0) flag=true;
			con.commit();
			con.setAutoCommit(true);
			rs.close();
			psmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean delete(){
		boolean flag=false;
		try {
			String sql = "delete from se where num=?";
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, bv.getNum());
			int rs = psmt.executeUpdate();
			if(rs>0) flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public BBSVo view(){
		BBSVo vo = null;
		try {
			
			String sql3 ="select num,mid,pwd,title,content,sedate from se where num=?";
			psmt = con.prepareStatement(sql3);
			psmt.setInt(1, bv.getNum());
			rs = psmt.executeQuery();
			//조금 이라도 속도를 빠르게 하기 위해 StringBuilder를 사용해서 clob타입 읽어 들임
			StringBuilder sb = null;
			if(rs.next()){
				Reader rd  = rs.getCharacterStream("content");
				sb = new StringBuilder();
				char[] buff = new char[1024];
				int read=0;
				while((read = rd.read(buff,0,1024))!=-1){
					sb.append(buff,0,read);
				}
				vo = new BBSVo(rs.getInt("num"), rs.getString("mid"), rs.getString("pwd"), 
						rs.getString("title"), sb, rs.getString("sedate"));
			}
			rs.close();
			psmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return vo;
	}
	
	public ArrayList<BBSVo> lists(){
		ArrayList<BBSVo> lists = new ArrayList<BBSVo>();
		try {
			String sql = "select num,mid,title,sedate from se order by num desc";
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
			while(rs.next()){
				BBSVo vo = new BBSVo();
				vo.setNum(rs.getInt("num"));
				vo.setMid(rs.getString("mid"));
				vo.setTitle(rs.getString("title"));
				vo.setSedate(rs.getString("sedate"));
				lists.add(vo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return lists;
	}
	
}
